<?php
/**
 * Created by PhpStorm.
 * User: 戎大富
 * Date: 2019/6/12
 * Time: 16:02
 */
namespace app\admin\controller;
use think\Db;
class Report extends Base {
    public function index(){
        if(request()->isAjax()){
            $where = array();
            $params = input('param.');
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            //日期
            if(isset($params['add_start']) && $params['add_start'] != '' && isset($params['add_end']) && $params['add_end'] != ''){
                $where['a.rhis_time'] = ['between', [$params['add_start'], $params['add_end']]];
            }
            if(isset($params['add_start']) && $params['add_start'] != '' && isset($params['add_end']) && $params['add_end'] == ''){
                $where['a.rhis_time'] = ['>=', $params['add_start']];
            }if(isset($params['add_start']) && $params['add_start'] == '' && isset($params['add_end']) && $params['add_end'] != ''){
                $where['a.rhis_time'] = ['<=', $params['add_end']];
            }

            //关键字
            if(isset($params['coupon_name']) && $params['coupon_name'] != ''){
                $keyword1 = $params['coupon_name'];
                $where['a.user_id|a.user_username|a.user_phone'] = array('like',"%$keyword1%");
            }
            //学校
            if(isset($params['user_school']) && $params['user_school'] != ''){
                $where['uuu.user_school'] = $params['user_school'];
            }
            if(isset($params['province']) && $params['province'] != ''){
                $where['uuu.user_sheng'] =$params['province'] ;
            }
            if(isset($params['city']) && $params['city'] != ''){
                $where['uuu.user_shi'] = $params['city'];
            }
            if(isset($params['district']) && $params['district'] != ''){
                $where['uuu.user_qu'] = $params['district'];
            }
            $count = Db::name('reading_history')->alias('a')
                ->join('up_u_user uuu','uuu.user_id = a.rhis_user_id','left')
                ->join('up_area ua','ua.district_id = uuu.user_sheng','left')
                ->join('up_area ua1','ua1.district_id = uuu.user_shi','left')
                ->join('up_area ua2','ua2.district_id = uuu.user_qu','left')
                ->join('up_school us','us.school_id = uuu.user_school','left')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::name('reading_history')->alias('a')
                ->join('up_u_user uuu','uuu.user_id = a.rhis_user_id','left')
                ->join('up_area ua','ua.district_id = uuu.user_sheng','left')
                ->join('up_area ua1','ua1.district_id = uuu.user_shi','left')
                ->join('up_area ua2','ua2.district_id = uuu.user_qu','left')
                ->join('up_school us','us.school_id = uuu.user_school','left')
                ->field('a.*,uuu.user_id,uuu.user_nickname,us.school_name,ua.district,ua1.district as district1,ua2.district as district2')
                ->page($Nowpage,$limits)
                ->where($where)
//                ->order('user_status desc')
                ->select();
            foreach ($lists as $key=>&$vo){
                $aa = array();
                $bb = array();
                $vo['address'] = $vo['district'].$vo['district1'].$vo['district2'];
                $info = Db::table('up_grade_class')->alias('a')
                    ->join('up_grade ug','ug.grade_id = a.gs_grade_id','left')
                    ->join('up_class uc','uc.class_id = a.gs_class_id','left')
                    ->field('ug.grade_name,uc.class_name')
                    ->where('a.gs_user_id',$vo['user_id'])
                    ->select();
                foreach ($info as $key1=>&$vo1){
                    $aa[] = $vo1['grade_name'];
                    $bb[] = $vo1['class_name'];
                }
                $vo['info1'] = implode(',',$aa);
                $vo['info2'] = implode(',',$bb);
            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $where1['school_status']  = 1;
        $where1['school_del']  = 1;
        $info1 = Db::table('up_school')->where($where1)->select();
        $province = Db::table('up_area')->where('level', 1)->select();
        $this->assign('province', $province);
        $this->assign('info1',$info1);
        return $this->fetch();
    }

    /**
     * 批量删除通知
     * @return \think\response\Json
     */
    public function payfeesDel(){
        $ids = input('ids');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择要删除的通知']);
        }
        $result = Db::table('up_reading_subject')->where('rhis_id', 'IN', $ids)->delete();
        if($result){
            return json(['code' => '200', 'msg' => '删除成功']);
        }else{
            return json(['code' => 100, 'msg' => '删除失败']);
        }
    }

    public function excelIn(){
        $ids = input('param.ids');
        if($ids == ''){
            return json(['code' => 100, 'msg' => '请选择数据']);
        }
        $where['a.rhis_id'] = array('IN',$ids);
        $lists = Db::name('reading_history')->alias('a')
            ->join('up_u_user uuu','uuu.user_id = a.rhis_user_id','left')
            ->join('up_area ua','ua.district_id = uuu.user_sheng','left')
            ->join('up_area ua1','ua1.district_id = uuu.user_shi','left')
            ->join('up_area ua2','ua2.district_id = uuu.user_qu','left')
            ->join('up_school us','us.school_id = uuu.user_school','left')
            ->field('a.*,uuu.user_id,uuu.user_nickname,us.school_name,ua.district,ua1.district as district1,ua2.district as district2')
            ->where($where)
//                ->order('user_status desc')
            ->select();
        foreach ($lists as $key=>&$vo){
            $aa = array();
            $bb = array();
            $vo['address'] = $vo['district'].$vo['district1'].$vo['district2'];
            $info = Db::table('up_grade_class')->alias('a')
                ->join('up_grade ug','ug.grade_id = a.gs_grade_id','left')
                ->join('up_class uc','uc.class_id = a.gs_class_id','left')
                ->field('ug.grade_name,uc.class_name')
                ->where('a.gs_user_id',$vo['user_id'])
                ->select();
            foreach ($info as $key1=>&$vo1){
                $aa[] = $vo1['grade_name'];
                $bb[] = $vo1['class_name'];
            }
            $vo['info1'] = implode(',',$aa);
            $vo['info2'] = implode(',',$bb);
            unset($lists[$key]['rhis_id']);
            unset($lists[$key]['rhis_game_id']);
            unset($lists[$key]['rhis_book_game']);
            unset($lists[$key]['rhis_type']);
            unset($lists[$key]['user_id']);
            unset($lists[$key]['district']);
            unset($lists[$key]['district1']);
            unset($lists[$key]['district2']);
        }
        $cellname = [
            ['rhis_user_id','用户ID',20,'LEFT'],
            ['rhis_book_pass','通过书籍数量',15,'LEFT'],
            ['rhis_game_pass','闯关总数',15,'LEFT'],
            ['rhis_exp_all','总金币',15,'LEFT'],
            ['rhis_score_all','总经验',15,'LEFT'],
            ['rhis_coupon','优惠卷金额',15,'LEFT'],
            ['rhis_time','闯关时间',15,'LEFT'],
            ['user_nickname','姓名',15,'LEFT'],
            ['school_name','学校',15,'LEFT'],
            ['address','地区',15,'LEFT'],
            ['info1','年纪',15,'LEFT'],
            ['info2','班级',15,'LEFT'],
        ];
        $res = exportExcel('报表管理','',$cellname,$lists);
        return json($res);
    }
}